package com.example.provider;

import java.util.ArrayList;
import java.util.List;

import android.app.Activity;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;
import android.widget.Toast;

import com.example.entity.DataTransalte;
import com.example.entity.Examples;
import com.example.entity.Groups;
import com.example.entity.Langs;
import com.example.entity.Means;
import com.example.entity.Words;

public class Provider extends SQLiteOpenHelper {
	
	private static final int DATABASE_VERSION = 1;
	private static final String DATABASE_NAME = "vocabularye";
	private static final String TBL_WORDS = "WORDS";
	private static final String TBL_GROUPS = "GROUPS";
	private static final String TBL_EXAMPLE = "EXAMPLES";
	private static final String TBL_LANGS = "LANGS";
	private static final String TBL_MEANS = "MEANS";
	private static final String LOG = "DatabaseHelper";
	
	/**
	 * 
	 * @param context
	 */
	public Provider(Context context) {
		super(context, DATABASE_NAME, null, DATABASE_VERSION);
	}

	/**
	 * excute delete DB
	 * @param fileName
	 * @return
	 */
	public Boolean excuteDeleteDB(String fileName){
		return true;
	}
	
	/**
	 * create table words
	 * @return
	 */
	private static final String doCreateTableWords(){
		StringBuilder sql = new StringBuilder(1000);
		sql.append("CREATE TABLE WORDS (");
		sql.append(" keyword TEXT PrimaryKey, ");
		sql.append(" idlang TEXT PrimaryKey, ");
		sql.append(" ismine TEXT, ");
		sql.append(" pronoun TEXT, ");
		sql.append(" groupId TEXT, ");
		sql.append(" sound TEXT, ");
		sql.append(" dateinsert TEXT, ");
		sql.append(" dateupdate TEXT, ");
		sql.append(" datedelete TEXT ) ");
		return sql.toString();
	}
	
	/**
	 * create table langs
	 * @return
	 */
	private static final String doCreateTableLangs(){
		StringBuilder sql = new StringBuilder(1000);
		sql.append("CREATE TABLE LANGS (");
		sql.append(" langid TEXT PrimaryKey, ");
		sql.append(" name TEXT, ");
		sql.append(" dateinsert TEXT, ");
		sql.append(" dateupdate TEXT, ");
		sql.append(" datedelete TEXT ) ");
		return sql.toString();
	}


	/**
	 * create table means
	 * @return
	 */
	private static final String doCreateTableMeans(){
		StringBuilder sql = new StringBuilder(1000);
		sql.append("CREATE TABLE MEANS (");
		sql.append(" keyword TEXT PrimaryKey, ");
		sql.append(" idlang TEXT PrimaryKey, ");
		sql.append(" kindword TEXT, ");
		sql.append(" content TEXT, ");
		sql.append(" dest TEXT, ");
		sql.append(" dateinsert TEXT, ");
		sql.append(" dateupdate TEXT, ");
		sql.append(" datedelete TEXT) ");
		return  sql.toString();
	}
	
	/**
	 * create table Examples
	 * @return
	 */
	private static final String doCreateTableExample(){
		StringBuilder sql = new StringBuilder(1000);
		sql.append("CREATE TABLE EXAMPLES (");
		sql.append(" keyword TEXT PrimaryKey, ");
		sql.append(" idlang PrimaryKey, ");
		sql.append(" content TEXT, ");
		sql.append(" dateinsert TEXT, ");
		sql.append(" dateupdate TEXT, ");
		sql.append(" datedelete TEXT) ");
		
		return sql.toString();
	}
	
	private static final String doCreateTableGroups(){
		StringBuilder sql = new StringBuilder(1000);
		sql.append("CREATE TABLE GROUPS (");
		sql.append(" idgroup TEXT PrimaryKey, ");
		sql.append(" idlang TEXT PrimaryKey, ");
		sql.append(" name TEXT, ");
		sql.append(" dateinsert TEXT, ");
		sql.append(" dateupdate TEXT, ");
		sql.append(" datedelete TEXT ) ");
		return sql.toString();
	}

	@Override
	public void onCreate(SQLiteDatabase db) {
		// TODO Auto-generated method stub
		db.execSQL(doCreateTableWords());
		db.execSQL(doCreateTableLangs());
		db.execSQL(doCreateTableMeans());
		db.execSQL(doCreateTableExample());
		db.execSQL(doCreateTableGroups());
	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		// TODO Auto-generated method stub
		db.execSQL("DROP TABLE IF EXISTS WORDS ");
		db.execSQL("DROP TABLE IF EXISTS LANGS ");
		db.execSQL("DROP TABLE IF EXISTS MEANS ");
		db.execSQL("DROP TABLE IF EXISTS EXAMPLES ");
		db.execSQL("DROP TABLE IF EXISTS GROUPS ");
		// create new tables
		onCreate(db);
	}
	
	
	/**
	 * create a word
	 * @param word
	 * @return
	 */
	public long createWords(Words word) {
		SQLiteDatabase db = this.getWritableDatabase();
		ContentValues values = new ContentValues();
		values.put("keyword", word.getKeyword());
		values.put("idlang", word.getIdlang());
		values.put("ismine", word.getIsmine());
		values.put("groupid", word.getGroupId());
		values.put("pronoun", word.getPronoun());
		values.put("sound", word.getSound());
		values.put("dateinsert", word.getDateinsert());
		values.put("dateupdate", word.getDateupdate());
		values.put("datedelete", word.getDatedelete());
		// insert row
		long word_id = db.insert(TBL_WORDS, null, values);
		return word_id;
	}
	
	/**
	 * create a word
	 * @param word
	 * @return
	 */
	public String createGroups(Groups group) {
		SQLiteDatabase db = this.getWritableDatabase();
		ContentValues values = new ContentValues();
		
		
		
		values.put("idgroup", group.getIdGroup());
		values.put("idlang", group.getIdlang());
		values.put("name", group.getGroupName());
		
		values.put("dateinsert", group.getDateinsert());
		values.put("dateupdate", group.getDateupdate());
		values.put("datedelete", group.getDatedelete());
		// insert row
		db.insert(TBL_GROUPS, null, values);
		return group.getIdGroup();
	}
	
	/**
	 * get group by keyword
	 * @param name
	 * @return
	 */
	public Groups getGroup(String nameGroup) {
		SQLiteDatabase db = this.getReadableDatabase();
		String selectQuery = "SELECT  * FROM " + TBL_GROUPS + " WHERE name = '"
				 + nameGroup + "'";
		Log.e(LOG, selectQuery);
		Cursor c = db.rawQuery(selectQuery, null);
		if (c != null){
			c.moveToFirst();
			Groups group = new Groups();
			while (c.isAfterLast() == false){
				group.setIdlang(c.getString(c.getColumnIndex("idlang")));
				group.setIdGroup(c.getString(c.getColumnIndex("idgroup")));
				group.setGroupName(c.getString(c.getColumnIndex("name")));
				group.setDateinsert(c.getString(c.getColumnIndex("dateinsert")));
				group.setDateupdate(c.getString(c.getColumnIndex("dateupdate")));
				group.setDatedelete(c.getString(c.getColumnIndex("datedelete")));
				return group;
			}
		}

		return null;
	}
	
	/**
	 * getting all kindWord
	 * */
	public List<Groups> getAllGroups() {
		List<Groups> groups = new ArrayList<Groups>();
		String selectQuery = "SELECT  * FROM " + TBL_GROUPS;

		Log.e(LOG, selectQuery);

		SQLiteDatabase db = this.getReadableDatabase();
		Cursor c = db.rawQuery(selectQuery, null);

		// looping through all rows and adding to list
			if (c != null){
				c.moveToFirst();
				while (c.isAfterLast() == false){
					Groups group = new Groups();
					group.setIdlang(c.getString(c.getColumnIndex("idlang")));
					group.setIdGroup(c.getString(c.getColumnIndex("idgroup")));
					group.setGroupName(c.getString(c.getColumnIndex("name")));
					group.setDateinsert(c.getString(c.getColumnIndex("dateinsert")));
					group.setDateupdate(c.getString(c.getColumnIndex("dateupdate")));
					group.setDatedelete(c.getString(c.getColumnIndex("datedelete")));
					groups.add(group);
					c.moveToNext();
				}
			}

		return groups;
	}
	
	/**
	 * update word
	 * @param act
	 * @param keyword
	 * @param idlang
	 */
	public void UpdateWords(Activity act, String keyword, String idlang, String idGroup)
	{
		SQLiteDatabase db = this.getWritableDatabase();
		ContentValues values = new ContentValues();
		values.put("ismine", "true");
		values.put("groupid", idGroup);
		int row = db.update(TBL_WORDS, values, "keyword ='" + keyword +"' and "+
									"idlang='" + idlang+"'" , null);
		if (row >=0)
			Toast.makeText(act.getApplicationContext(), "Done!", Toast.LENGTH_LONG).show();
	}
	
	public ArrayList<String> getMyWord(String GroupName) {
		
		ArrayList<String> tmp = new ArrayList<String>();
		Groups group = getGroup(GroupName);
		SQLiteDatabase db = this.getReadableDatabase();
		String selectQuery = "SELECT keyword FROM " + TBL_WORDS + 
		" WHERE ismine = 'true' and groupId = '" + group.getIdGroup() + "'";
		Cursor c = db.rawQuery(selectQuery, null);
		
		Log.i("Translator", "XXXXXXXXXX getMyword Row=" + c.getCount());
		if (c != null){
			c.moveToFirst();
			while (c.isAfterLast() == false){
				//Log.i("Translator", "XXXXXXXXXX getMyword Row=" + c.getString(0));
				
				tmp.add(c.getString(0));
				c.moveToNext();
			}
		}

		return tmp;
	}
	
	/**
	 * create a country
	 * @param lang
	 * @return
	 */
	public long createLang(Langs lang) {
		SQLiteDatabase db = this.getWritableDatabase();
		ContentValues values = new ContentValues();
		values.put("langid", lang.getLangid());
		values.put("name ", lang.getName());
		
		values.put("dateinsert", lang.getDateinsert());
		values.put("dateupdate", lang.getDateupdate());
		values.put("datedelete", lang.getDatedelete());
		// insert row
		long decrition_id = db.insert(TBL_LANGS, null, values);
		return decrition_id;
	}
	
	/**
	 * create mean of a word
	 * @param mean
	 * @return
	 */
	public long createMeans(Means mean) {
		SQLiteDatabase db = this.getWritableDatabase();
		ContentValues values = new ContentValues();
		values.put("keyword", mean.getKeyword());
		values.put("idlang", mean.getIdlang());
		values.put("kindword", mean.getKindword());
		values.put("content", mean.getContent());
		values.put("dest", mean.getDest());
		values.put("dateinsert", mean.getDateinsert());
		values.put("dateupdate", mean.getDateupdate());
		values.put("datedelete", mean.getDatedelete());
		// insert row
		long decrition_id = db.insert(TBL_MEANS, null, values);
		return decrition_id;
	}
	
	/**
	 * create example of a word
	 * @param exam
	 * @return
	 */
	public long createExample(Examples exam) {
		SQLiteDatabase db = this.getWritableDatabase();
		ContentValues values = new ContentValues();
		values.put("keyword ", exam.getKeyword());
		values.put("idlang", exam.getIdlang());
		values.put("content ", exam.getContent());
		values.put("dateinsert", exam.getDateinsert());
		values.put("dateupdate", exam.getDateupdate());
		values.put("datedelete", exam.getDatedelete());
		// insert row
		long decrition_id = db.insert(TBL_EXAMPLE, null, values);
		return decrition_id;
	}
	
	
	/**
	 * get word by keyword
	 * @param name
	 * @return
	 */
	public Words getWord(String keyword) {
		SQLiteDatabase db = this.getReadableDatabase();
		String selectQuery = "SELECT  * FROM " + TBL_WORDS + " WHERE keyword = '"
				 + keyword.trim() + "' limit 1";

		Log.e(LOG, selectQuery);
		Cursor c = db.rawQuery(selectQuery, null);
		Words word = null;
		if (c != null){
			c.moveToFirst();
			
			while (c.isAfterLast() == false){
				word = new Words();
				word.setKeyword((c.getString(c.getColumnIndex("keyword"))));
				word.setIdlang(c.getString(c.getColumnIndex("idlang")));
				word.setIsmine(c.getString(c.getColumnIndex("ismine")));
				word.setPronoun(c.getString(c.getColumnIndex("pronoun")));
				word.setPronoun(c.getString(c.getColumnIndex("groupId")));
				word.setSound(c.getString(c.getColumnIndex("sound")));
				word.setDateinsert(c.getString(c.getColumnIndex("dateinsert")));
				word.setDateupdate(c.getString(c.getColumnIndex("dateupdate")));
				word.setDatedelete(c.getString(c.getColumnIndex("datedelete")));
				c.moveToNext();
			}
		}

		return word;
	}
	
	/**
	 * get Mean by idlang and keyword
	 * @param keyWord
	 * @param idLang
	 * @return
	 */
	public Means getMean(String keyWord,String idLang) {
		SQLiteDatabase db = this.getReadableDatabase();
		String selectQuery = "SELECT  * FROM " + TBL_MEANS + " WHERE keyword = '"
				 + keyWord.trim() + "' and idlang ='" + idLang + "' limit 1";
		Log.e(LOG, selectQuery);
		
		Cursor c = db.rawQuery(selectQuery, null);
		if (c != null){
			c.moveToFirst();
			Means means = new Means();
			while (c.isAfterLast() == false){
				
				means.setIdmean((c.getString(c.getColumnIndex("idmean"))));
				means.setKeyword(c.getString(c.getColumnIndex("keyword")));
				means.setIdlang(c.getString(c.getColumnIndex("idlang")));
				means.setKindword(c.getString(c.getColumnIndex("kindword")));
				means.setContent(c.getString(c.getColumnIndex("content")));
				means.setDest(c.getString(c.getColumnIndex("dest")));
				means.setDateinsert(c.getString(c.getColumnIndex("dateinsert")));
				means.setDateupdate(c.getString(c.getColumnIndex("dateupdate")));
				means.setDatedelete(c.getString(c.getColumnIndex("datedelete")));
				return means;
			}
		}

		return null;
	}
	
	
	/**
	 * get example with keyword and langid
	 * @param keyWord
	 * @param idLang
	 * @return
	 */
	public Examples getExample(String keyWord,String idLang) {
		SQLiteDatabase db = this.getReadableDatabase();
		String selectQuery = "SELECT  * FROM " + TBL_MEANS + " WHERE keyword = '"
				 + keyWord.trim() + "' and idlang ='" + idLang + "' limit 1";
		Log.e(LOG, selectQuery);
		
		Cursor c = db.rawQuery(selectQuery, null);
		if (c != null){
			c.moveToFirst();
			Examples exam = new Examples();
			while (c.isAfterLast() == false){
				exam.setIdexample((c.getString(c.getColumnIndex("idexample"))));
				exam.setKeyword(c.getString(c.getColumnIndex("keyword")));
				exam.setIdlang(c.getString(c.getColumnIndex("idlang")));
				exam.setContent(c.getString(c.getColumnIndex("content")));
				exam.setDateinsert(c.getString(c.getColumnIndex("dateinsert")));
				exam.setDateupdate(c.getString(c.getColumnIndex("dateupdate")));
				exam.setDatedelete(c.getString(c.getColumnIndex("datedelete")));
				return exam;
			}
		}

		return null;
	}
	
	
	/**
	 * get example with keyword and langid
	 * @param keyWord
	 * @param idLang
	 * @return
	 */
	public DataTransalte translate(String keyWord) {
		SQLiteDatabase db = this.getReadableDatabase();
		
		Log.e(LOG, searchSql(keyWord));
		
		Cursor c = db.rawQuery(searchSql(keyWord), null);
		
		if (c != null){
			c.moveToFirst();
			while (c.isAfterLast() == false){
				DataTransalte transalate = new DataTransalte();
				Examples exam = new Examples();
				Means mean = new Means();
				mean.setContent(c.getString(c.getColumnIndex("content_mean")));
				exam.setContent(c.getString(c.getColumnIndex("content_example")));
				transalate.setExam(exam);
				transalate.setMean(mean);
				return transalate;
			}
		}

		return null;
	}
	
	/**
	 * sql translate
	 * @param keyWord
	 * @return
	 */
	private String searchSql(String keyWord){
		
		StringBuilder sql = new StringBuilder();
		sql.append(" select distinct ");
		sql.append(" mean.content as content_mean, ");
		sql.append(" exam.content as content_example ");
		sql.append(" from WORDS word ");
		sql.append(" inner join MEANS mean ");
		sql.append(" on word.keyword = mean.keyword and word.idlang = mean.idlang ");
		sql.append(" inner join EXAMPLES exam ");
		sql.append(" on word.keyword = exam.keyword and word.idlang = exam.idlang ");
		sql.append(" where ");
		sql.append(" word.keyword ='" + keyWord + "' ");
		
		return sql.toString();
	}
	
	
	/**
	 * getting all kindWord
	 * */
	public List<Langs> getAllLangs() {
		List<Langs> langs = new ArrayList<Langs>();
		String selectQuery = "SELECT  * FROM " + TBL_LANGS;

		Log.e(LOG, selectQuery);

		SQLiteDatabase db = this.getReadableDatabase();
		Cursor c = db.rawQuery(selectQuery, null);

		// looping through all rows and adding to list
		if (c.moveToFirst()) {
			do {
				
				Langs lang = new Langs();
				lang.setLangid(c.getString((c.getColumnIndex("langid"))));
				lang.setName((c.getString(c.getColumnIndex("name"))));
				lang.setDateinsert(c.getString(c.getColumnIndex("dateinsert")));
				lang.setDateupdate(c.getString(c.getColumnIndex("dateupdate")));
				lang.setDatedelete(c.getString(c.getColumnIndex("datedelete")));
				// adding to todo list
				langs.add(lang);
			} while (c.moveToNext());
		}

		return langs;
	}
}
